﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using UygulamaYetkiService.Classes;

namespace UygulamaYetkiService
{
    public class DuyuruDao : IDisposable
    {
        public List<Duyuru> DuyuruGetir()
        {
            List<Duyuru> duyuruList = null;
            try
            {
                using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TakbisBilgiSorguYetkiConnection"].ToString()))
                {
                    string sql = string.Format(@"select ID,Baslik,Icerik,AktifMi,KayitTarih
                                                 from TakbisBilgiSorguYetki..Duyuru with(nolock) order by ID DESC ");
                    using (SqlCommand cmd = new SqlCommand(sql))
                    {
                        using (SqlDataAdapter sda = new SqlDataAdapter())
                        {
                            duyuruList = new List<Duyuru>();
                            cmd.Connection = con;
                            sda.SelectCommand = cmd;
                            cmd.CommandTimeout = 60000;
                            DataTable dt = new DataTable();
                            sda.Fill(dt);
                            if (dt.Rows.Count > 0)
                            {
                                foreach (DataRow dr in dt.Rows)
                                {
                                    Duyuru duyuru = new Duyuru(dr);
                                    duyuruList.Add(duyuru);
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                using (ExceptionLogDao el = new ExceptionLogDao())
                {
                    el.HataLogYaz(DateTime.Now.ToString() + "  " + ex.ToString());
                }
            }
            return duyuruList;
        }

        public List<Duyuru> AktifDuyuruGetir()
        {
            List<Duyuru> duyuruList = null;
            try
            {
                using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TakbisBilgiSorguYetkiConnection"].ToString()))
                {
                    string sql = string.Format(@"select ID,Baslik,Icerik,AktifMi,KayitTarih
                                                 from TakbisBilgiSorguYetki..Duyuru with(nolock) where AktifMi=1 order by ID DESC");
                    using (SqlCommand cmd = new SqlCommand(sql))
                    {
                        using (SqlDataAdapter sda = new SqlDataAdapter())
                        {
                            duyuruList = new List<Duyuru>();
                            cmd.Connection = con;
                            sda.SelectCommand = cmd;
                            cmd.CommandTimeout = 60000;
                            DataTable dt = new DataTable();
                            sda.Fill(dt);
                            if (dt.Rows.Count > 0)
                            {
                                foreach (DataRow dr in dt.Rows)
                                {
                                    Duyuru duyuru = new Duyuru(dr);
                                    duyuruList.Add(duyuru);
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                using (ExceptionLogDao el = new ExceptionLogDao())
                {
                    el.HataLogYaz(DateTime.Now.ToString() + "  " + ex.ToString());
                }
            }
            return duyuruList;
        }

        public Duyuru DuyuruGetirIDDen(long ID)
        {
            Duyuru duyuru = null;
            try
            {
                using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TakbisBilgiSorguYetkiConnection"].ToString()))
                {
                    string sql = string.Format(@"select ID,Baslik,Icerik,AktifMi,KayitTarih
                                                 from TakbisBilgiSorguYetki..Duyuru with(nolock)
                                                 where ID={0}", ID);
                    using (SqlCommand cmd = new SqlCommand(sql))
                    {
                        using (SqlDataAdapter sda = new SqlDataAdapter())
                        {
                            cmd.Connection = con;
                            sda.SelectCommand = cmd;
                            cmd.CommandTimeout = 60000;
                            DataTable dt = new DataTable();
                            sda.Fill(dt);
                            if (dt.Rows.Count > 0)
                            {
                                foreach (DataRow dr in dt.Rows)
                                    duyuru = new Duyuru(dr);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                using (ExceptionLogDao el = new ExceptionLogDao())
                {
                    el.HataLogYaz(DateTime.Now.ToString() + "  " + ex.ToString());
                }
            }
            return duyuru;
        }
        public Duyuru DuyuruGuncelle(Duyuru duyuru)
        {
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TakbisBilgiSorguYetkiConnection"].ToString()))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    con.Open();
                    SqlTransaction transaction = con.BeginTransaction("trans");
                    try
                    {
                        cmd.CommandText = "update Duyuru with(updlock) set Baslik=@Baslik,Icerik=@Icerik,AktifMi=@AktifMi where ID=@ID";
                        cmd.Parameters.Add("@ID", duyuru.ID);
                        cmd.Parameters.Add("@Baslik", duyuru.Baslik);
                        cmd.Parameters.Add("@Icerik", duyuru.Icerik);
                        cmd.Parameters.Add("@AktifMi", duyuru.AktifMi);
                        cmd.Connection = con;
                        cmd.Transaction = transaction;
                        cmd.ExecuteScalar();
                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        using (ExceptionLogDao el = new ExceptionLogDao())
                        {
                            el.HataLogYaz(DateTime.Now.ToString() + "  " + ex.ToString());
                        }
                    }
                    finally
                    {
                        con.Close();
                        con.Dispose();
                        cmd.Dispose();
                    }
                    return duyuru;
                }
            }
        }

        public Duyuru DuyuruKaydet(Duyuru duyuru)
        {
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TakbisBilgiSorguYetkiConnection"].ToString()))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    con.Open();
                    SqlTransaction transaction = con.BeginTransaction("trans");
                    try
                    {
                        cmd.CommandText = @"insert into Duyuru with(updlock) (Baslik,Icerik,AktifMi,KayitTarih) values (@Baslik,@Icerik,@AktifMi,GETDATE()); SELECT SCOPE_IDENTITY()";
                        cmd.Parameters.Add("@Baslik", duyuru.Baslik);
                        cmd.Parameters.Add("@Icerik", duyuru.Icerik);
                        cmd.Parameters.Add("@AktifMi", duyuru.AktifMi);
                        cmd.Connection = con;
                        cmd.Transaction = transaction;
                        duyuru.ID = Convert.ToInt64(cmd.ExecuteScalar());
                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        using (ExceptionLogDao el = new ExceptionLogDao())
                        {
                            el.HataLogYaz(DateTime.Now.ToString() + "  " + ex.ToString());
                        }
                    }
                    finally
                    {
                        con.Close();
                        con.Dispose();
                        cmd.Dispose();
                    }
                    return duyuru;
                }
            }
        }

        public void Dispose()
        {
        }
    }
}
